package com.deng.etl.excel.impl;

import java.io.File;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import com.deng.common.SQLHelper;
import com.deng.etl.excel.IExcelRule;
import com.deng.util.DataFormater;
import com.deng.util.DateTools;

public class ChengBenHeSuanImpl extends SQLHelper implements IExcelRule {

//	private String compare = "operate_code,operate_name,item_code,item_name,fee";
//	private String table = "t_org_operate_item_count_month";
	
	public void importData(Workbook wb, String type_name,String id) {
		try{			
//			File f = new File(fileName);
//			System.out.println(f.canRead());
//			System.out.println(f.canWrite());
//			Workbook wb=Workbook.getWorkbook(f);
//			if("0".equals(update)){//纯导入
				Connection conn = getConnect();
				
				Sheet sh=wb.getSheet(0);
				int rownum = sh.getRows();
				
				StringBuffer sb = new StringBuffer();
				for(int i=2;i<rownum;i++){
					Cell c1 = sh.getCell(0,i);
					Cell c2 = sh.getCell(1,i);
					if(sb.indexOf("'"+(getYM1(c1.getContents(),c2.getContents()))+"',") == -1){
						sb.append("'"+(getYM1(c1.getContents(),c2.getContents()))+"',");
					}
				}
				String delSQL = "delete t_org_operate_item_count_month" +
						"	where stat_time in ("+sb.toString().substring(0, sb.length()-1)+")";
				updateData(conn, delSQL);//删除stat_Time当天
				
				conn.setAutoCommit(false);
				String insertSQL = "insert into t_org_operate_item_count_month (operate_code,operate_name,item_code," +
						"	item_name,fee,stat_time,create_time) " +
						"	values(?,?,?,?,?,?,'"+DateTools.dtsWithTime(new Date())+"')";
				PreparedStatement pst =conn.prepareStatement(insertSQL);
				for(int i=2;i<rownum;i++){
					Cell c1 = sh.getCell(0,i);
					if(c1 == null){
						continue;
					}
					if(c1.getContents() == null || "".equals(c1.getContents())){
						continue;
					}
					Cell c2 = sh.getCell(1,i);
					Cell c3 = sh.getCell(2,i);
					Cell c4 = sh.getCell(3,i);
					Cell c5 = sh.getCell(4,i);
					Cell c6 = sh.getCell(5,i);
					Cell c7 = sh.getCell(6,i);
					
					Date stat_time = DateTools.stdJustDate(getYM1(c1.getContents(),c2.getContents()));
					
					String operate_code = c3.getContents();
					String operate_name = c4.getContents();
					String item_code = c5.getContents();
					String item_name = c6.getContents();					
					BigDecimal fee = new BigDecimal(c7.getContents().trim());
					
					
					
					pst.setString(1, operate_code);
					pst.setString(2, operate_name);
					pst.setString(3, item_code);
					pst.setString(4, item_name);
					pst.setBigDecimal(5, fee);
					pst.setDate(6, new java.sql.Date(stat_time.getTime()));
					pst.addBatch();
					
					System.out.println(operate_code+"  "+operate_name+"  "+item_code+"  "+item_name+"  "+fee+"  "+stat_time);
				}
				pst.executeBatch();
				conn.commit();
				pst.close();
				freeConnection(conn);
//			}else{
////				Connection conn = getConnect();
////				Sheet sh=wb.getSheet(0);
////				int rownum = sh.getRows();
////				
////				for(int i=1;i<rownum;i++){					
////					Cell c1 = sh.getCell(0,i);
////					if(c1 == null){
////						continue;
////					}
////					if(c1.getContents() == null || "".equals(c1.getContents())){
////						continue;
////					}
////					Cell c2 = sh.getCell(1,i);
////					Cell c3 = sh.getCell(2,i);
////					Cell c4 = sh.getCell(3,i);
////					Cell c5 = sh.getCell(4,i);
////					
////					String operate_code = c1.getContents();
////					String operate_name = c2.getContents();
////					String item_code = c3.getContents();
////					String item_name = c4.getContents();
////					BigDecimal fee = new BigDecimal(c5.getContents().trim());
////					
////					
////					String sqlSearch = "select * from t_org_operate_item_count_month" +
////							"	where stat_time = '"+stat_time+"' " +
////							"	and operate_code ='"+operate_code+"'" +
////							"	and item_code='"+item_code+"'";
////					PreparedStatement pst=conn.prepareStatement(sqlSearch, 
////								ResultSet.TYPE_SCROLL_INSENSITIVE, 
////								ResultSet.CONCUR_UPDATABLE);
////					ResultSet rs=pst.executeQuery();
////					if(rs.next()){
////						rs.updateBigDecimal(4, fee);
////						rs.updateRow();
////					}else{
////						String insertSQL = "insert into t_org_operate_item_count_month (operate_code,operate_name,item_code," +
////						"	item_name,fee,stat_time,create_time) " +
////								"	values('"+operate_code+"','"+operate_name+"'" +
////								"	,'"+item_code+"','"+item_name+"',"+fee+",'"+stat_time+"','"+DateTools.dtsWithTime(new Date())+"')";
////						updateData(conn, insertSQL);
////					}
////				}
////				freeConnection(conn);
//			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	private String getYM1(String y,String m){
		return y.trim()+"-"+m.trim()+"-1";
	}
}
